﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using log4net;


namespace DevMaker.CM.Mailing.App_code {
    public class BoletaDao {

        private static readonly ILog Log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public List<Boleta> ObtieneBoletas() {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ToString());
            var boletas = new List<Boleta>();
            try {
                var cine = ObtieneCine();
                var usuarios = ObtieneUsuarios();
                conn.Open();
                var query =
                    @" Select * 
                         from tblTrans_Cash ,viewCabBookingCMK
                         where 1=1
                            and viewCabBookingCMK.vwTransT_TransC_lgnNumber = tblTrans_Cash.TransC_lgnNumber
                            and (tblTrans_Cash.TransC_strBKSource like '%OVS%' or  tblTrans_Cash.TransC_strBKSource like '%WWW%')
                            and tblTrans_Cash.TickNum_strStyleCode like '<TED VERSION=%'
                            and viewCabBookingCMK.vwBookingH_strStatus='P' AND viewCabBookingCMK.vwBookingD_strStatus ='P' and (viewCabBookingCMK.vwPrice_strCode like '%OVS%' or viewCabBookingCMK.vwPrice_strCode like '%WEB%')
                            [TransC_lgnNumber]
	                        ORDER BY tblTrans_Cash.TransC_lgnNumber asc ,viewCabBookingCMK.vwBookingD_intSequence desc
                          ";

                if (!String.IsNullOrEmpty(ConfigurationManager.AppSettings["TransC_lgnNumber"])) {
                    query = query.Replace("[TransC_lgnNumber]",
                                          " and tblTrans_Cash.TransC_lgnNumber=" +
                                          ConfigurationManager.AppSettings["TransC_lgnNumber"]);
                } else {
                    query = query.Replace("[TransC_lgnNumber]",
                                          "and CONVERT(datetime,TransC_dtmDateTime) between @fechaInicio and @fechaFin");
                    //query = query.Replace("[TransC_lgnNumber]", String.Empty);

                }

                var fechahoy = DateTime.Now;
                if (!String.IsNullOrEmpty(ConfigurationManager.AppSettings["fechaHoy"])) {
                    fechahoy = DateTime.ParseExact(ConfigurationManager.AppSettings["fechaHoy"], "yyyy-MM-dd",
                                                   System.Globalization.CultureInfo.InvariantCulture);

                }

                var fechaInicio = new DateTime(fechahoy.Year, fechahoy.Month, fechahoy.Day, 6, 0, 0);
                var fechaFin = fechaInicio.AddDays(1);

                var fechaInicioParam = new SqlParameter("@fechaInicio", SqlDbType.DateTime);
                fechaInicioParam.Value = fechaInicio;

                var fechaFinParam = new SqlParameter("@fechaFin", SqlDbType.DateTime);
                fechaFinParam.Value = fechaFin;
                var cmd = new SqlCommand(query, conn);

                cmd.Parameters.Add(fechaInicioParam);
                cmd.Parameters.Add(fechaFinParam);

                cmd.Prepare();
                var boleta = new Boleta();
                var reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    try {
                        if (boleta.TransC_lgnNumber != Convert.ToInt64(reader["TransC_lgnNumber"])) {
                            boleta = new Boleta();
                            boleta.Cine = cine;
                            boleta.TransC_lgnNumber = Convert.ToInt64(reader["TransC_lgnNumber"]);
                            boleta.TransC_intSequence = Convert.ToInt64(reader["TransC_intSequence"]);
                            boleta.TransC_dtmDateTime = Convert.ToDateTime(reader["TransC_dtmDateTime"]);
                            boleta.TransC_curValue = Convert.ToDecimal(reader["TransC_curValue"]);
                            boleta.TickNum_strStyleCode = reader["TickNum_strStyleCode"].ToString();
                            boleta.vwBookingH_strName = reader["vwBookingH_strName"].ToString();
                            boleta.vwBookingH_strBookingId = reader["vwBookingH_strBookingId"].ToString();
                            boleta.vwPGroup_strCode = reader["vwPGroup_strCode"].ToString();
                            boleta.vwPrice_strCode = reader["vwPrice_strCode"].ToString();
                            boleta.vwFilm = reader["vwFilm"].ToString();
                            boleta.vwFilm = reader["vwPrice_strCode"].ToString();
                            boleta.TransC_strReceiptNumber = reader["TransC_strReceiptNumber"].ToString();
                            boleta.vwBookingH_strEmail = reader["vwBookingH_strEmail"].ToString();
                            boleta.vwPickupWorkstationName = reader["vwPickupWorkstationName"].ToString();
                            boleta.TransC_intBKPickupUser = reader["TransC_intBKPickupUser"].ToString();


                            if (String.IsNullOrEmpty(reader["TransC_intBKPickupUser"].ToString())) {
                                boleta.Operador = "User Internet" + boleta.Cine.Cinema_strBranchNo;
                            } else {
                                var u = usuarios.Find(foo => (foo.User_intUserNo == (boleta.TransC_intBKPickupUser.Trim())));
                                if (u != null)
                                    boleta.Operador = u.User_strFirstName + " " + u.User_strLastName;
                            }


                            boleta.vwBookingH_intPickupUser = reader["vwBookingH_intPickupUser"].ToString();
                            boleta.vwBookingH_intTicketsPrt = Convert.ToInt64(reader["vwBookingH_intTicketsPrt"].ToString());
                            if (!String.IsNullOrEmpty(reader["TransC_dtmBKDateCollected"].ToString()))
                                boleta.TransC_dtmBKDateCollected = Convert.ToDateTime(reader["TransC_dtmBKDateCollected"]);
                            boleta.TransC_strBKPickupWorkstn = reader["TransC_strBKPickupWorkstn"].ToString();
                            if (String.IsNullOrEmpty(boleta.TransC_strBKPickupWorkstn))
                                boleta.TransC_strBKPickupWorkstn = "CL" + boleta.Cine.strBranchNo + "TA01";
                            boleta.BoletaDetalles = ObtieneBoletaDetalle(boleta.TransC_lgnNumber);

                            var cantidadEntradas = boleta.BoletaDetalles.Aggregate(0,
                                                                                   (current, detalle) =>
                                                                                   (int)(current + detalle.Cantidad));
                            var cargoServicio = new BoletaDetalle();
                            cargoServicio.NombreItem = "Cargo por servicio";
                            cargoServicio.Cantidad = cantidadEntradas;
                            cargoServicio.PrecioUnitario = 300;
                            cargoServicio.ValorLinea = 300 * cantidadEntradas;
                            boleta.BoletaDetalles.Add(cargoServicio);
                            boletas.Add(boleta);
                        }
                    } catch (Exception excep) {
                        Log.Error(excep);
                    }
                }
            } catch (Exception ex) {
                Log.Error(ex);
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["maxResult"])) {
                var max = Convert.ToInt16(ConfigurationManager.AppSettings["maxResult"]);
                if (boletas.Count < max)
                    max = (short)boletas.Count;
                boletas = boletas.GetRange(0, max);
            }
            return boletas;
        }

        public List<BoletaDetalle> ObtieneBoletaDetalle(Int64 TransC_lgnNumber) {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ToString());
            var detalles = new List<BoletaDetalle>();
            try {
                conn.Open();
                var query =
                   @"SELECT SUM(TransT_intNoOfSeats) as CANT_ITEM,Price_strDescription as NOMB_ITEM,
                         CAST(TransT_curValueEach as Integer) as PREC_UNIC, CAST(SUM(TransT_intNoOfSeats) * TransT_curValueEach as Integer) as VALO_LINE
                        From tblTrans_Ticket
                        INNER JOIN tblPrice ON tblTrans_Ticket.PGroup_strCode = tblPrice.PGroup_strCode
	                        AND tblTrans_Ticket.Price_strCode = tblPrice.Price_strCode
	                        WHERE TransT_strType = 'P'  AND TransT_lgnNumber =  @TransT_lgnNumber
	                        GROUP BY Price_strDescription,TransT_curValueEach";

                var transTLgnNumberParam = new SqlParameter("@TransT_lgnNumber", SqlDbType.BigInt);
                transTLgnNumberParam.Value = TransC_lgnNumber;
                var cmd = new SqlCommand(query, conn);
                cmd.Parameters.Add(transTLgnNumberParam);
                cmd.Prepare();
                var reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    var boletaDetalle = new BoletaDetalle();
                    boletaDetalle.Cantidad = Convert.ToInt64(reader["CANT_ITEM"]);
                    boletaDetalle.NombreItem = reader["NOMB_ITEM"].ToString();
                    boletaDetalle.PrecioUnitario = Convert.ToInt64(reader["PREC_UNIC"]);
                    boletaDetalle.ValorLinea = Convert.ToInt64(reader["VALO_LINE"]);
                    detalles.Add(boletaDetalle);
                }
            } catch (Exception ex) {
                Log.Error(ex);
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return detalles;

        }

        public Cine ObtieneCine() {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ToString());
            var cine = new Cine();
            try {
                conn.Open();
                const string query = @"select * from tblCinema ";


                var cmd = new SqlCommand(query, conn);
                cmd.Prepare();
                var reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    cine = new Cine();
                    cine.Cinema_strCode = reader["Cinema_strCode"].ToString();
                    cine.Cinema_strBranchNo = reader["Cinema_strBranchNo"].ToString();
                    cine.Cinema_strName = reader["Cinema_strName"].ToString();
                    cine.Cinema_strShortName = reader["Cinema_strShortName"].ToString();
                    cine.Cinema_strNameLegal = reader["Cinema_strNameLegal"].ToString();
                    cine.Cinema_strAddress1 = reader["Cinema_strAddress1"].ToString();

                }
            } catch (Exception ex) {
                Log.Error(ex);
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return cine;
        }

        public List<Usuario> ObtieneUsuarios() {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CM_Connection"].ToString());
            var usuarios = new List<Usuario>();
            try {
                conn.Open();
                const string query = @"select * from tblUser ";


                var cmd = new SqlCommand(query, conn);
                cmd.Prepare();
                var reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    var usuario = new Usuario();
                    usuario.User_intUserNo = reader["User_intUserNo"].ToString();
                    usuario.User_intPIN = reader["User_intPIN"].ToString();
                    usuario.User_strFirstName = reader["User_strFirstName"].ToString();
                    usuario.User_strLastName = reader["User_strLastName"].ToString();
                    usuarios.Add(usuario);
                }
            } catch (Exception ex) {
                Log.Error(ex);
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return usuarios;
        }

        public static void InsertTracking(Boleta boleta, String acccion, Exception ex) {
            var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Tracking_Connection"].ToString());

            try {
                conn.Open();
                const string query = @"INSERT INTO [dbo].[track]
                                               ([cine]
                                               ,[codigo_transaccion]
                                               ,[numero_boleta]
                                               ,[email]
                                               ,[path_archivo]
                                               ,[accion]
                                               ,[accion_exitosa]
                                               ,[excepcion])
                                         VALUES
                                               (@cine
                                               ,@codigo_transaccion
                                               ,@numero_boleta
                                               ,@email
                                               ,@path_archivo
                                               ,@accion
                                               ,@accion_exitosa
                                               ,@excepcion)
                                    ";


                var cmd = new SqlCommand(query, conn);
                var cineParam = new SqlParameter("@cine", SqlDbType.VarChar) { Value = boleta.Cine.Cinema_strCode, Size = 20 };
                cmd.Parameters.Add(cineParam);

                var codigoTransaccionParam = new SqlParameter("@codigo_transaccion", SqlDbType.VarChar) { Value = boleta.TransC_lgnNumber, Size = 100 };
                cmd.Parameters.Add(codigoTransaccionParam);

                var boletaParam = new SqlParameter("@numero_boleta", SqlDbType.VarChar) { Value = boleta.TransC_strReceiptNumber, Size = 100 };
                cmd.Parameters.Add(boletaParam);

                var emailParam = new SqlParameter("@email", SqlDbType.VarChar) { Value = boleta.vwBookingH_strEmail, Size = 150 };
                cmd.Parameters.Add(emailParam);

                var pathArchivoParam = new SqlParameter("@path_archivo", SqlDbType.VarChar) { Value = boleta.PathFile, Size = 100 };
                cmd.Parameters.Add(pathArchivoParam);

                var accionParam = new SqlParameter("@accion", SqlDbType.VarChar) { Value = acccion, Size = 10 };
                cmd.Parameters.Add(accionParam);

                var accionExistosa = true;
                if (ex != null)
                    accionExistosa = false;

                var accionExitosaParam = new SqlParameter("@accion_exitosa", SqlDbType.Bit) { Value = accionExistosa, Size = 1 };
                cmd.Parameters.Add(accionExitosaParam);

                var exMassage = String.Empty;
                if (ex != null) {
                    exMassage = ex.Message + " ----" + ex.StackTrace;
                    if (exMassage.Length > 1500)
                        exMassage = exMassage.Substring(0, 1500);
                }

                var excepcionParam = new SqlParameter("@excepcion", SqlDbType.VarChar) { Value = exMassage, Size = 1500 };
                cmd.Parameters.Add(excepcionParam);

                cmd.Prepare();
                cmd.ExecuteNonQuery();

            } catch (Exception exe) {
                Log.Error(exe);
            } finally {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
    }
}